import pool from '../config/db.js';

class Order {
  // 获取订单列表
  static async getOrders(userId, { page, limit, status, startDate, endDate }) {
    try {
      let query = `
        SELECT 
          o.*,
          COUNT(*) OVER() as total_count
        FROM orders o
        WHERE o.user_id = ?
      `;
      
      const params = [userId];
      
      if (status) {
        query += ' AND o.status = ?';
        params.push(status);
      }
      
      if (startDate && endDate) {
        query += ' AND o.created_at BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }
      
      query += ' ORDER BY o.created_at DESC LIMIT ? OFFSET ?';
      params.push(limit, (page - 1) * limit);
      
      const [rows] = await pool.query(query, params);
      
      // 获取订单商品详情
      const orders = await Promise.all(rows.map(async (order) => {
        const [items] = await pool.query(`
          SELECT 
            oi.*,
            p.name,
            p.image,
            p.price
          FROM order_items oi
          JOIN products p ON oi.product_id = p.id
          WHERE oi.order_id = ?
        `, [order.id]);
        
        return {
          ...order,
          items
        };
      }));
      
      return {
        orders,
        total: rows.length > 0 ? rows[0].total_count : 0
      };
    } catch (error) {
      console.error('Error in getOrders:', error);
      throw error;
    }
  }

  // 获取最近订单
  static async getRecentOrders(userId) {
    try {
      const [rows] = await pool.query(`
        SELECT 
          o.*,
          oi.product_id,
          oi.quantity,
          p.name,
          p.image,
          p.price
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN products p ON oi.product_id = p.id
        WHERE o.user_id = ?
        ORDER BY o.created_at DESC
        LIMIT 5
      `, [userId]);
      
      // 组织订单数据结构
      const orders = [];
      let currentOrder = null;
      
      rows.forEach(row => {
        if (!currentOrder || currentOrder.id !== row.id) {
          currentOrder = {
            id: row.id,
            order_no: row.order_no,
            status: row.status,
            total_amount: row.total_amount,
            created_at: row.created_at,
            items: []
          };
          orders.push(currentOrder);
        }
        
        currentOrder.items.push({
          product_id: row.product_id,
          quantity: row.quantity,
          name: row.name,
          image: row.image,
          price: row.price
        });
      });
      
      return orders;
    } catch (error) {
      console.error('Error in getRecentOrders:', error);
      throw error;
    }
  }

  // 确认收货
  static async confirmReceive(userId, orderId) {
    try {
      const [result] = await pool.query(
        'UPDATE orders SET status = "completed" WHERE id = ? AND user_id = ? AND status = "shipped"',
        [orderId, userId]
      );
      
      if (result.affectedRows === 0) {
        throw new Error('订单状态更新失败');
      }
      
      return true;
    } catch (error) {
      console.error('Error in confirmReceive:', error);
      throw error;
    }
  }

  // 取消订单
  static async cancelOrder(userId, orderId) {
    try {
      const [result] = await pool.query(
        'UPDATE orders SET status = "cancelled" WHERE id = ? AND user_id = ? AND status = "pending"',
        [orderId, userId]
      );
      
      if (result.affectedRows === 0) {
        throw new Error('订单取消失败');
      }
      
      return true;
    } catch (error) {
      console.error('Error in cancelOrder:', error);
      throw error;
    }
  }

  // 添加获取订单列表的方法
  static async getOrders(userId, { status }) {
    try {
      // 使用参数化查询防止 SQL 注入
      const params = [userId];
      let statusCondition = '';
      
      if (status) {
        statusCondition = 'AND o.status = ?';
        params.push(status);
      }

      const query = `
        SELECT 
          o.id,
          o.order_no,
          o.status,
          o.total_amount,
          o.created_at,
          COALESCE(
            JSON_ARRAYAGG(
              JSON_OBJECT(
                'id', oi.id,
                'product_id', oi.product_id,
                'quantity', oi.quantity,
                'price', oi.price,
                'name', p.name,
                'image', p.image
              )
            ),
            '[]'
          ) as items
        FROM orders o
        LEFT JOIN order_items oi ON o.id = oi.order_id
        LEFT JOIN products p ON oi.product_id = p.id
        WHERE o.user_id = ? ${statusCondition}
        GROUP BY o.id
        ORDER BY o.created_at DESC
      `;

      const [rows] = await pool.query(query, params);
      
      return {
        orders: rows.map(order => ({
          ...order,
          items: JSON.parse(order.items).map(item => ({
            ...item,
            image: item.image?.startsWith('http') 
              ? item.image 
              : `/uploads/${item.image}`
          }))
        })),
        total: rows.length
      };
    } catch (error) {
      console.error('Error in getOrders:', error);
      throw error;
    }
  }

  static async getOrdersWithPagination(userId, { page, limit, status, startDate, endDate }) {
    try {
      let query = `
        SELECT 
          o.*,
          GROUP_CONCAT(
            JSON_OBJECT(
              'id', oi.id,
              'product_id', oi.product_id,
              'quantity', oi.quantity,
              'price', oi.price,
              'name', p.name,
              'image', p.image
            )
          ) as items
        FROM orders o
        LEFT JOIN order_items oi ON o.id = oi.order_id
        LEFT JOIN products p ON oi.product_id = p.id
        WHERE o.user_id = ?
      `;
      
      const params = [userId];
      
      if (status) {
        query += ' AND o.status = ?';
        params.push(status);
      }
      
      if (startDate && endDate) {
        query += ' AND o.created_at BETWEEN ? AND ?';
        params.push(startDate, endDate);
      }
      
      query += ' GROUP BY o.id ORDER BY o.created_at DESC';
      query += ' LIMIT ? OFFSET ?';
      params.push(limit, (page - 1) * limit);
      
      const [rows] = await pool.query(query, params);
      const [countResult] = await pool.query(
        'SELECT COUNT(*) as total FROM orders WHERE user_id = ?',
        [userId]
      );
      
      const orders = rows.map(order => ({
        ...order,
        items: order.items 
          ? JSON.parse(`[${order.items}]`).map(item => ({
              ...item,
              image: item.image && !item.image.startsWith('http') 
                ? `/uploads/${item.image}`
                : item.image
            }))
          : []
      }));
      
      return {
        orders,
        total: countResult[0].total
      };
    } catch (error) {
      console.error('Error in getOrdersWithPagination:', error);
      throw error;
    }
  }
}

export default Order; 